﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

namespace BHA
{
    public partial class Main
    {
        private void Main_Startup(object sender, System.EventArgs e)
        {
        }

        private void Main_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.M_Description.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.M_Description_Change);
            this.M_PartNo.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.M_PartNo_Change);
            this.Startup += new System.EventHandler(Main_Startup);
            this.Shutdown += new System.EventHandler(Main_Shutdown);
        }

        #endregion

        const int BLANK_MAX = 2;

        //static bool bOnDeleteRow = false;
        private void M_Description_Change(Excel.Range Target)
        {
            SetNewCurrentCulture();
            try
            {
                Application.EnableEvents = false;
                this.EnableCalculation = false;
                Application.ScreenUpdating = false;
                //if (Target.Count
                //if (bOnDeleteRow) return; List<Excel.Range> list = new List<Excel.Range>();
                foreach (Excel.Range m in Application.Intersect(Target, M_Description.RefersToRange))
                {   // Duyet qua cac cell trong cot Description
                    if (string.IsNullOrWhiteSpace(m.Value))
                    {   // Neu null hoac space thi clear nguyen row tru depth
                        m.Offset[0, M_PartNo.Column].ClearContents();
                        m.Offset[0, M_Co.Column].ClearContents();
                        m.Offset[0, M_TopConnect.Column].ClearContents();
                        m.Offset[0, M_BotConnect.Column].ClearContents();
                        m.Offset[0, M_OD.Column].ClearContents();
                        m.Offset[0, M_ID.Column].ClearContents();
                        m.Offset[0, M_Length.Column].ClearContents();
                        // list.Add(r);
                        continue;
                    }

                    int blankcount = 0; // Count Blank BLANK_MAX thi tu dong thoat (2 hang trang tu dong dung duyet)
                    foreach (Excel.Range db in Globals.DB.DB_Description)
                    {   // Duyet qua het cot Description trong Database
                        if (string.IsNullOrWhiteSpace(db.Value))
                        {   // Neu trong BLANK_MAX row thi thoat
                            blankcount++;
                            if (blankcount == BLANK_MAX) break;
                        }
                        else blankcount = 0;

                        if (db.Value == m.Value) // FURTHER NEED TO CHANGE HERE TO USE REGEX
                        {   // Neu Tim Duoc trong Database Cell db co Description giong voi Cell m trong Main
                            int above = 0, below = 0;
                            foreach (Excel.Range mc in db.Offset[0, Globals.DB.DB_Symbol.Column - db.Column].MergeArea)
                            {
                                // Neu Row Thap Hon c thi Insert Main above r va Copy with Shape
                                // Neu Row = thi Copy with Shape
                                // Neu Row > Hon thi Insert Main below r va Copy with Shape
                                if (mc.Row < db.Row) { m.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown); above++; }
                                else if (mc.Row > db.Row) { this.Rows[m.Row + 1].Insert(Excel.XlInsertShiftDirection.xlShiftDown); below++; }
                            }

                            for (int i = above; i > 0; i--) { CopyDB(true, m.Row - i, db.Row - i); }
                            for (int i = 0; i <= below; i++) { CopyDB(true, m.Row + i, db.Row + i); }
                        }
                    }
                }
                //foreach (Excel.Range r in list) { bOnDeleteRow = true; r.EntireRow.Delete(); bOnDeleteRow = false; }

            }

            catch (Exception ex)
            {

            }
            finally
            {
                this.EnableCalculation = true;
                Application.EnableEvents = true;
                Application.ScreenUpdating = true;
                ResetCurrentCulture();
            }
        }

        #region CultureInfo
        System.Globalization.CultureInfo oldCI;
        void SetNewCurrentCulture()
        {
            oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
        }
        void ResetCurrentCulture()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
        }
        #endregion

        private void M_PartNo_Change(Excel.Range Target)
        {

        }

        void CopyCell(Excel.Range r, Excel.Range c)
        {
            r.Value = c.Value;
            r.Interior.Color = c.Interior.Color;
            r.Font.Color = c.Font.Color;
            r.Font.Bold = c.Font.Bold;
        }

        void CopyBackgroundFont(Excel.Range r, Excel.Range c)
        {

        }

        void CopyDB(bool WithShape, int MainRow, int DBRow)
        {
            Excel.Range m = this.Cells[MainRow, M_Description.Column];
            Excel.Range db = Globals.DB.Cells[DBRow, Globals.DB.DB_Description.Column];
            m.Value = db.Value;

            m.RowHeight = db.RowHeight; // Cell Description trong Main co chieu cao bang Cell Description trong Database
            m.Resize[1, M_PartNo.Column - m.Column].Interior.Color = db.Interior.Color; // Cac cell tiep sau den cell PartNo se co cung mau
            m.Font.Color = db.Font.Color; // Giong luon Font Color
            m.Font.Bold = db.Font.Bold; // Giong luon Font Bold

            CopyCell(this.Cells[MainRow, M_PartNo.Column], Globals.DB.Cells[DBRow, Globals.DB.DB_PartNo.Column]);
            CopyCell(this.Cells[MainRow, M_Co.Column], Globals.DB.Cells[DBRow, Globals.DB.DB_Co.Column]);
            CopyCell(this.Cells[MainRow, M_TopConnect.Column], Globals.DB.Cells[DBRow, Globals.DB.DB_TopConnect.Column]);

            CopyCell(this.Cells[MainRow, M_BotConnect.Column], Globals.DB.Cells[DBRow, Globals.DB.DB_BotConnect.Column]);
            CopyCell(this.Cells[MainRow, M_OD.Column], Globals.DB.Cells[DBRow, Globals.DB.DB_OD.Column]);
            CopyCell(this.Cells[MainRow, M_ID.Column], Globals.DB.Cells[DBRow, Globals.DB.DB_ID.Column]);

            db = Globals.DB.Cells[DBRow, Globals.DB.DB_Length.Column]; db.Copy();
            m = this.Cells[MainRow, M_Length.Column]; m.PasteSpecial(Excel.XlPasteType.xlPasteFormulas);
            CopyBackgroundFont(m, db);

            CopyBackgroundFont(this.Cells[MainRow, M_Depth.Column], this.Cells[MainRow, M_Length.Column]); // Rieng Cell Depth se co dinh dang giong cell Length truoc do

            foreach (Excel.Shape ms in this.Shapes)
            {   // Xoa luon Shape tuong ung trong M_String (bang cach duyet)
                try
                {   // Tiep theo duyet het cac shape trong Main de xoa shape nao cung hang voi cell dang doi Desrciption
                    if (ms.TopLeftCell.Row == m.Row && ms.TopLeftCell.Column == M_String.Column) ms.Delete();
                }
                catch (Exception e) { } // Incase getTopLeftCell throw exception
            }

            if (!WithShape) return;

            foreach (Excel.Shape dbs in Globals.DB.Shapes)
            {   // Copy Shape tuong ung trong Database vao M_String

                //if (Globals.DB.Cells[s.TopLeftCell.Row, c.Column].Value == c.Value)
                if (dbs.TopLeftCell.Row == db.Row)
                {   // Dung Cell thi past Shape
                    dbs.Copy();
                    Excel.Range mr = Cells[m.Row, M_String.Column];
                    mr.PasteSpecial();

                    // Chinh Shape ra chinh giua Cell Vua Paste
                    foreach (Excel.Shape ms in this.Shapes)
                    {   // Duyet het mang de tim ra Cell tim.
                        try
                        {   // Tiep theo duyet het cac shape trong Main de xoa shape nao cung hang voi cell dang doi Description
                            if (ms.TopLeftCell.Row == mr.Row && ms.TopLeftCell.Column == mr.Column)
                                ms.Left = (float)(ms.Left + (mr.Width - ms.Width) / 2);
                        }
                        catch (Exception e) { } // Incase getTopLeftCell throw exception
                    }
                }
            }
        }
    }
}
